import pymysql
from pymysql.cursors import DictCursor

class DBCommon:
    def __init__(self, host, user, password, database, port=3306, charset='utf8mb4'):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset

    def execute(self, sql, params=None, commit=True):
        """执行SQL语句"""
        try:
            with pymysql.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                port=self.port,
                charset=self.charset,
                cursorclass=DictCursor
            ) as conn:
                with conn.cursor() as cursor:
                    cursor.execute(sql, params)
                    if commit:
                        conn.commit()
                        return cursor.rowcount
                    return cursor
        except Exception as e:
            print(f"数据库操作错误: {e}")
            if commit:
                conn.rollback()
            raise

    def insert(self, table, data):
        """插入单条数据"""
        keys = ', '.join(data.keys())
        values = ', '.join(['%s'] * len(data))
        sql = f"INSERT INTO {table} ({keys}) VALUES ({values})"
        return self.execute(sql, tuple(data.values()))

    def batch_insert(self, table, fields, data_list):
        """批量插入数据"""
        keys = ', '.join(fields)
        values = ', '.join(['%s'] * len(fields))
        sql = f"INSERT INTO {table} ({keys}) VALUES ({values})"
        params = [tuple(item.values()) for item in data_list]
        with pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database,
            port=self.port,
            charset=self.charset,
            cursorclass=DictCursor
        ) as conn:
            with conn.cursor() as cursor:
                cursor.executemany(sql, params)
                conn.commit()
                return cursor.rowcount

    def select_one(self, table, fields='*', condition=None, order_by=None, params=None):
        """查询单条数据"""
        sql = f"SELECT {fields} FROM {table}"
        if condition:
            sql += f" WHERE {condition}"
        if order_by:
            sql += f" ORDER BY {order_by}"
        sql += " LIMIT 1"
        cursor = self.execute(sql, params, commit=False)
        return cursor.fetchone()

    def select_all(self, table, fields='*', condition=None, order_by=None, limit=None, params=None):
        """查询多条数据"""
        sql = f"SELECT {fields} FROM {table}"
        if condition:
            sql += f" WHERE {condition}"
        if order_by:
            sql += f" ORDER BY {order_by}"
        if limit:
            sql += f" LIMIT {limit}"
        cursor = self.execute(sql, params, commit=False)
        return cursor.fetchall()

    def update(self, table, data, condition, params=None):
        """更新数据"""
        set_clause = ', '.join([f"{key} = %s" for key in data])
        sql = f"UPDATE {table} SET {set_clause} WHERE {condition}"
        params = tuple(data.values()) + (params if params else ())
        return self.execute(sql, params)

    def delete(self, table, condition, params=None):
        """删除数据"""
        sql = f"DELETE FROM {table} WHERE {condition}"
        return self.execute(sql, params)

    def count(self, table, condition=None, params=None):
        """统计数量"""
        sql = f"SELECT COUNT(*) as total FROM {table}"
        if condition:
            sql += f" WHERE {condition}"
        result = self.select_one(sql, params=params)
        return result['total'] if result else 0

# 使用示例
if __name__ == "__main__":
    # 初始化数据库连接
    db = DBCommon(
        host='localhost',
        user='root',
        password='password',
        database='your_database'
    )

    # 插入数据
    insert_data = {'userId': 1001}
    db.insert('t_session', insert_data)

    # 查询数据
    result = db.select_all('t_session', condition='userId = %s', params=(1001,))
    print("查询结果:", result)

    # 更新数据
    update_data = {'userId': 1002}
    db.update('t_session', update_data, 'id = %s', (1,))

    # 删除数据
    db.delete('t_session', 'id = %s', (1,))